﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using CWB.Common.Logging;
using CWB.Common.Utilities;
using CWB.Web.CacheLayer;
using CWB.Common.DTO;
using CWB.Web.Models;
using System.Linq;
using CWB.Common.Constant;

namespace CWB.Web.DB
{
    public class DbModuleBbu
    {
        public string username;
        public string password;
        public int sn;
        public string chk;
    }

    public class DbTableBbu : BaseDAO
    {
        private readonly static ISysLog log = SysLog.GetLogger(typeof(DbTableBbu));

        public DbTableBbu()
        {
            base.init(typeof(DbTableBbu).ToString(), DataBaseName.Org);
        }

        private List<DbModuleBbu> getModuleBbuCallBack(SQLiteDataReader sdr)
        {
            List<DbModuleBbu> listbbu = new List<DbModuleBbu>() { };
            if (sdr.HasRows)
            {
                while (sdr.Read())
                {
                    listbbu.Add(new DbModuleBbu()
                    {
                        password = sdr["pwd"].ToString()
                        ,
                        chk = sdr["chk"].ToString()
                        ,
                        sn = Convert.ToInt32(sdr["sn"].ToString())
                        ,
                        username = sdr["username"].ToString()
                    });
                }
            }
            return listbbu;
        }

        public DbModuleBbu getBbuBySn(int sn)
        {
            const string strCMD = "select * from bbu where sn = @sn";
            List<SQLiteParameter> listPara = new List<SQLiteParameter>() { };
            listPara.Add(new SQLiteParameter("@sn", sn));
            List<DbModuleBbu> bbu = ExecuteReader<DbModuleBbu>(CommandType.Text, strCMD, listPara, getModuleBbuCallBack);
            if (bbu.Count > 0)
            {
                return bbu[0];
            }
            else
            {
                Exception e = new Exception(string.Format("Data not found. bbu.sn : [{0}]", sn));
                log.Exception(e);
                throw (e);
            }
        }

        public DbModuleBbu getBbuByName(string username)
        {
            const string strCMD = "select * from bbu where username= @username";
            List<SQLiteParameter> listPara = new List<SQLiteParameter>() { };
            listPara.Add(new SQLiteParameter("@username", username));
            List<DbModuleBbu> bbu = ExecuteReader<DbModuleBbu>(CommandType.Text, strCMD, listPara, getModuleBbuCallBack);
            if (bbu.Count > 0)
            {
                return bbu[0];
            }
            else
            {
                Exception e = new Exception(string.Format("Data not found. bbu.username : [{0}]", username));
                log.Exception(e);
                throw (e);
            }
        }

        //verified backoffice user.
        public bool VerifyBouLogin(string username, string password)
        {
            const string strCMD = "select sn from bbu where username = @username and pwd = @password;";
            List<SQLiteParameter> listPara = new List<SQLiteParameter>() { };
            listPara.Add(new SQLiteParameter("@username", username));
            listPara.Add(new SQLiteParameter("@password", password));
            int icnt = ExecuteReaderCount(strCMD, listPara);
            return (icnt > 0);
        }

        //update chk value and put into cache.
        public void UpdateBouChk(DbModuleBbu b)
        {
            const string strCMD = "update bbu set chk = @chk where sn = @sn";
            List<SQLiteParameter> listPara = new List<SQLiteParameter>() { };
            listPara.Add(new SQLiteParameter("@chk", b.chk));
            listPara.Add(new SQLiteParameter("@sn", b.sn));
            int irnt = ExecuteNonQuery(strCMD, listPara);
            BouCache.Add(b);
        }

        //update chk value and put into cache.
        public void RemoveBouChk(string username)
        {
            CWB.Web.CacheLayer.BouCache.BouUserCacheInfo buc =  BouCache.Get(username);
            const string strCMD = "update bbu set chk = '' where sn = @sn";
            List<SQLiteParameter> listPara = new List<SQLiteParameter>() { };
            listPara.Add(new SQLiteParameter("@sn", buc.u.sn));
            int irnt = ExecuteNonQuery(strCMD, listPara);
            BouCache.Remove(username);
        }

        public bool CheckUserNameIsExist(string username)
        {
            int nums = ExecuteReaderCount<string>("bbu", "username", username);
            if (nums == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        public bool createBbu(string username, string password)
        {
            const string strCMD = "insert into bbu (username, pwd) values (@username, @pwd)";
            List<SQLiteParameter> listPara = new List<SQLiteParameter>() { };
            listPara.Add(new SQLiteParameter("@username", username));
            listPara.Add(new SQLiteParameter("@pwd", password));
            int icnt = ExecuteNonQuery(strCMD, listPara);
            return (icnt > 0);
        }

        public bool updateBbuBySN(int sn, string password)
        {
            const string strCMD = "update bbu set pwd = @pwd where sn = @sn";
            List<SQLiteParameter> listPara = new List<SQLiteParameter>() { };
            listPara.Add(new SQLiteParameter("@sn", sn));
            listPara.Add(new SQLiteParameter("@pwd", password));
            int icnt = ExecuteNonQuery(strCMD, listPara);
            return (icnt > 0);
        }

        public bool DeleteBbuBySn(int sn)
        {
            const string strCMD = "delete from bbu where sn = @sn";
            List<SQLiteParameter> listPara = new List<SQLiteParameter>() { };
            listPara.Add(new SQLiteParameter("@sn", sn));
            int icnt = ExecuteNonQuery(strCMD, listPara);
            return (icnt > 0);
        }

        public List<DbModuleBbu> getAllBbu(ref Paging pg, KeyValuePair<string, string> SearchPara, DBSortingPara dbSortingPara)
        {
            string sqlMain = "Select * from bbu";
            string sqlWehre = default(string);
            string sqlOrderby = default(string);
            List<SQLiteParameter> sqlPara = new List<SQLiteParameter>() { };
            if (!string.IsNullOrEmpty(SearchPara.Key) && !string.IsNullOrEmpty(SearchPara.Value))
            {
                string key = SearchPara.Key;
                sqlWehre = string.Format(" Where {0} = @{1}", key, key);
                sqlPara.Add(new SQLiteParameter(string.Format("@{0}", key), SearchPara.Value));
            }
            sqlOrderby = dbSortingPara.getOrderByHLQ();

            //get totalRecord
            string sqlCount = string.Format("select count(*) as rcnt from {0}", sqlMain.Split(' ').Last());
            string strCMD = string.Format("{0} {1}", sqlCount, sqlWehre);
            int count = ExecuteReaderCount(strCMD, sqlPara);
            pg.totalRecord = count;

            //get data
            strCMD = string.Format("{0} {1} {2} {3}", sqlMain, sqlWehre, sqlOrderby, pg.getLimitSql());
            List<DbModuleBbu> listbbu = ExecuteReader<DbModuleBbu>(CommandType.Text, strCMD, sqlPara, getModuleBbuCallBack);

            return listbbu;
        }
    }
}
